# -*- coding:utf8 -*- #
# -----------------------------------------------------------------------------------
# ProjectName:  smart-meter-iot-test-console
# FileName:     mySqlPool.py
# Author  :
# Datetime:     2023/3/22 10:01
# Description：
# -----------------------------------------------------------------------------------
# coding=utf-8
"""
使用DBUtils数据库连接池中的连接，操作数据库
OperationalError: (2006, ‘MySQL server has gone away’)
"""
import pymysql
import sshtunnel
from dbutils.pooled_db import PooledDB
from sshtunnel import SSHTunnelForwarder
from common.util.configUtil.configUtil import ConfigUtil
from common.util.jsonUtl.jsonUtil import JsonUtil
from common.util.logUtil.logUtil import LoggerUtil


class MysqlPoolUtil(object):
    __pool = None

    def __init__(self, logType: int = 1):
        self._conn = None
        self.cursor = None
        self.logger = LoggerUtil(logType)

    def connect(self):
        """
                    mincached:连接池中空闲连接的初始数量
                    maxcached:连接池中空闲连接的最大数量
                    maxshared:共享连接的最大数量
                    maxconnections:创建连接池的最大数量
                    blocking:超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
                    maxusage:单个连接的最大重复使用次数
                    setsession:optional list of SQL commands that may serve to prepare
                        the session, e.g. ["set datestyle to ...", "set time zone ..."]
                    reset:how connections should be reset when returned to the pool
                        (False or None to rollback transcations started with begin(),
                        True to always issue a rollback for safety's sake)
                    host:数据库ip地址
                    port:数据库端口
                    db:库名
                    user:用户名
                    passwd:密码
                    charset:字符编码
                """
        try:
            config = ConfigUtil()
            dataBaseEnvironment = config.get_database_status()
            if dataBaseEnvironment is True:
                #         SSH_Proxy_host = config.get_database_info("SSH_Proxy_host")
                #         SSH_Proxy_port = int(config.get_database_info("SSH_Proxy_port"))  # 端口号需要是整型
                #         SSH_Proxy_user = config.get_database_info("SSH_Proxy_user")
                #         SSH_Proxy_password = config.get_database_info("SSH_Proxy_password")
                #         Pro_host = config.get_database_info("Pro_host")
                #         Pro_port = int(config.get_database_info("Pro_port"))  # 端口号需要是整型
                #         Pro_user = config.get_database_info("Pro_user")
                #         Pro_password = config.get_database_info("Pro_password")
                #         Pro_database = config.get_database_info("Pro_database")
                #         server = SSHTunnelForwarder(
                #             (SSH_Proxy_host, SSH_Proxy_port),  # 这里写入B 跳板机IP、端口
                #             ssh_username=SSH_Proxy_user,  # 跳板机 用户名
                #             ssh_password=SSH_Proxy_password,  # 跳板机 密码
                #             remote_bind_address=(Pro_host, Pro_port),
                #             # 这里写入 C数据库的 IP、端口号
                #         )
                #         server.start()
                #         if not self.__pool:
                #             self.__class__.__pool = PooledDB(pymysql, mincached=10, maxcached=20, maxshared=10,
                #                                              maxconnections=200,
                #                                              blocking=True,
                #                                              maxusage=100, setsession=None, reset=True,
                #                                              host="127.0.0.1", port=server.local_bind_port, db=Pro_database,
                #                                              user=Pro_user, passwd=Pro_password,
                #                                              charset='utf8mb4'
                #                                              )
                #         self._conn = self.__pool.connection()
                #         self.cursor = self._conn.cursor()
                #         return self.cursor
                # except sshtunnel.HandlerSSHTunnelForwarderError as handlerError:
                #     print("线上数据库无法连接！")
                #     self.logger.error(f"sshtunnel.HandlerSSHTunnelForwarderError:  {handlerError}")
                #     self.connect()
                # except sshtunnel.BaseSSHTunnelForwarderError as baseError:
                #     print("线上数据库无法连接！")
                #     self.logger.error(f"sshtunnel.BaseSSHTunnelForwarderError:  {baseError}")
                #     self.connect()
                ProUser = config.get_database_info("Pro_user")
                ProPassword = config.get_database_info("Pro_password")
                ProHost = config.get_database_info("Pro_host")
                ProPort = int(config.get_database_info("Pro_port"))  # 端口号需要是整型
                ProDatabase = config.get_database_info("Pro_database")
                __connect = pymysql.Connect(user=ProUser, password=ProPassword, host=ProHost, port=ProPort,
                                            database=ProDatabase, charset='utf8mb4')
                __cursor = __connect.cursor()
                self.cursor = __cursor
                self._conn = __connect
                return __cursor
        except Exception as error:
            print("线上数据库无法连接！")
            self.logger.error(f"Exception:  {error}")
            self.connect()

    def close(self):
        try:
            self.cursor.close()
            self._conn.close()
        except Exception as e:
            print(e)
            self.logger.error(f"close:  {e}")
            raise e


def select_sql(sql, logType: int = 1) -> list:
    """查结果"""
    mysql = MysqlPoolUtil(logType)
    cursor = mysql.connect()
    cursor.execute(sql)
    result = cursor.fetchall()
    allResult = []
    for i in result:
        row_list = []
        for k in i:
            if k is "" or k is None:
                k = 0
            row_list.append(k)
        allResult.append(row_list)
    mysql.close()
    return allResult


if __name__ == "__main__":
    print(select_sql(JsonUtil().read_sql("select_collector_vendorKey").format("C45520230002")))
